﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.ServiceProcess;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Timers;

using CE.Mail;
using CE.InputOut;

namespace GG_CRM_Renewal_Reminder_MailService
{
    partial class SendRenewalReminderMail : ServiceBase
    {
        EventLog _eventLog = new EventLog();

        private int _emailIntervalSec = Convert.ToInt32(System.Configuration.ConfigurationSettings.AppSettings["EmailIntervalSec"].ToString());
        private int _timerNormalInterval = 10;
        string _serviceTitle = "GG-CRM Renewal Remainder Mail Service";

        public SendRenewalReminderMail()
        {
            InitializeComponent();
            if (!System.Diagnostics.EventLog.SourceExists("GG_CRM_Renewal_Reminder_MailService"))
                System.Diagnostics.EventLog.CreateEventSource("GG_CRM_Renewal_Reminder_MailService",
                                                                      _serviceTitle);
            _eventLog.Source = "GG_CRM_Renewal_Reminder_MailService";
        }

        public void StartService()
        {
            StartSendingEmail();
        }

        protected override void OnStart(string[] args)
        {
            timer1.Interval = _timerNormalInterval * 1000;
            timer1.Enabled = true;
        }

        protected override void OnStop()
        {
            timer1.Enabled = false;
            //_eventLog.WriteEntry("CX - Policy Listing OnStop", EventLogEntryType.Information);
        }

        private void Timer1_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            //timer1.Enabled = false;
            int NoOfDays = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["NoOfDays"].ToString());
            timer1.Interval = (24 * NoOfDays) * 60 * 60 * 1000;
            StartSendingEmail();
        }

        private DateTime dateTimeNow = MyMailManager.GetUTCDateTime();

        private void StartSendingEmail()
        {
            string _conString = "";
            SqlCommand _sqlCmd = null;
            SqlConnection _sqlConn = new SqlConnection();

            string _emailTitle = string.Empty;
            string[] fileList = new string[0];
            string _toemail = "";
            string _ccemail = "";
            string _bccemail = "";
            StringBuilder _emailBody;
            System.Text.StringBuilder _strEmailList = new StringBuilder();
            string _step = "Step 1";
            try
            {
                string IsLive = System.Configuration.ConfigurationManager.AppSettings["IsOnLive"].ToString();
                string IsSendEmailCustomer = System.Configuration.ConfigurationManager.AppSettings["IsSendEmailCustomer"].ToString();
                int NoOfDays = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["NoOfDays"].ToString());
                //NoOfDays = 1 = today
                _toemail = System.Configuration.ConfigurationSettings.AppSettings["ToEmail"].ToString();
                _ccemail = System.Configuration.ConfigurationSettings.AppSettings["CcEmail"].ToString();
                _bccemail = System.Configuration.ConfigurationSettings.AppSettings["BccEmail"].ToString();

                string _smtpAccount = System.Configuration.ConfigurationManager.AppSettings["SMTPAccount"].ToString();
                string _smtpAuthCode = System.Configuration.ConfigurationManager.AppSettings["SMTPAuthCode"].ToString();
                string _smtpInternalAccount = System.Configuration.ConfigurationManager.AppSettings["InternalSMTPAccount"].ToString();
                string _smtpInternalAuthCode = System.Configuration.ConfigurationManager.AppSettings["InternalSMTPAuthCode"].ToString();
                string _smtpReplyTo = System.Configuration.ConfigurationManager.AppSettings["SMTPReplyTo"].ToString();

                _conString = ConfigurationManager.ConnectionStrings["CX_CRMDBConnectionString"].ToString();

                _sqlConn.ConnectionString = _conString;
                DataSet _dsResult = new DataSet();
                _sqlCmd = new SqlCommand();
                _sqlCmd.Connection = _sqlConn;
                _sqlCmd.CommandType = CommandType.StoredProcedure;
                _sqlCmd.CommandText = "sprocGGCRMRenewalReminderList";
                SqlParameter _p1 = new SqlParameter("@NoOfDays", NoOfDays);
                _sqlCmd.Parameters.Add(_p1);

                SqlDataAdapter _sqlDa = new SqlDataAdapter(_sqlCmd);

                _sqlDa.Fill(_dsResult);

                //@Yui 20131120 send 1 email

                if (_dsResult != null)
                {
                    _step = "Step 2.1";
                    int _mailThreeSendCount = 0;
                    if (_dsResult.Tables[0].Rows.Count > 0)
                    {
                        _eventLog.WriteEntry(_serviceTitle + " Started at [" + dateTimeNow.ToString("dd/MMM/yyyy hh:mm:ss") + "].", EventLogEntryType.Information);
                    }
                    else
                    {
                        _eventLog.WriteEntry(_serviceTitle + " No Mail to send. [" + dateTimeNow.ToString() + "].", EventLogEntryType.Information);
                    }


                    try
                    {
                        //9/12/2556 save to tblWindowServiceLog
                        _sqlCmd = new SqlCommand();
                        _sqlCmd.Connection = _sqlConn;
                        _sqlCmd.CommandType = CommandType.StoredProcedure;
                        _sqlCmd.CommandText = "[sprocWindowServiceSaveLog]";
                        _sqlCmd.Parameters.AddWithValue("@ServiceName", "GG_CRM_Renewal_Reminder_MailService");
                        _sqlCmd.Parameters.AddWithValue("@Subject", _serviceTitle + " was started at " + MyMailManager.GetUTCDateTime().ToString("dd/MM/yyyy hh:mm:ss"));
                        _sqlCmd.Parameters.AddWithValue("@EmailCount", _dsResult.Tables[0].Rows.Count);
                        _sqlCmd.Parameters.AddWithValue("@ServiceStatus", 1);
                        _sqlCmd.Parameters.AddWithValue("@Status", 1);
                        _sqlConn.Open();
                        _sqlCmd.ExecuteNonQuery();
                        _sqlConn.Close();
                    }
                    catch (Exception ex)
                    {
                        string xx = ex.Message.ToString();
                    }

                    //_emailBody = new StringBuilder();
                    //_emailTitle = "<Started> " + _serviceTitle + " - " + dateTimeNow.ToString("dd-MMM-yyyy hh:mm tt");
                    //_emailBody.Append("Dear All,<br/><br/>");
                    //_emailBody.Append(_serviceTitle + " was started at " + dateTimeNow.ToString("dd/MMM/yyyy hh:mm:ss") + "<br/><br/>");

                    //_emailBody.Append("No. of days to batch : <b>" + NoOfDays.ToString() + "</b> days.<br/><br/>");

                    //if (_dsResult.Tables[0].Rows.Count > 0)
                    //{
                    //    _emailBody.Append("No. of 75 days earlier mail : <b>" + _dsResult.Tables[0].Rows.Count + "</b><br/>");
                        
                    //    _emailBody.Append("<b>Please check following customer list details as your reference.</b><br/><br/>");

                    //    if (_dsResult.Tables[0].Rows.Count > 0)
                    //    {
                    //        _emailBody.Append("<table border='1'>");
                    //        _emailBody.Append("<caption style='color:blue'><b>For 75 days earlier</b></caption>");
                    //        _emailBody.Append("<tr bgcolor='lightgray'><th>No.</th><th>Quote.Ref</th><th>Name</th><th>Car Plate</th><th>Policy No.</th><th>Coverage End Date</th></tr>");
                    //        for (int i = 0; i < _dsResult.Tables[0].Rows.Count; i++)
                    //        {
                    //            _emailBody.Append("<tr " + ((i % 2 == 0) ? "bgcolor='#ffffcc'" : "") + "><td>" + (i + 1).ToString() + "</td><td>" + _dsResult.Tables[0].Rows[i]["QuoteRefNo"] + "</td><td>" + _dsResult.Tables[0].Rows[i]["Name"] + "</th><td>" + _dsResult.Tables[0].Rows[i]["CarPlate"] + "</td><td>" + _dsResult.Tables[0].Rows[i]["PolicyNumber"] + "</td><td>" + Convert.ToDateTime(_dsResult.Tables[0].Rows[i]["CoverageStart"].ToString()).AddYears(1).ToString("dd-MMM-yyyy") + "</td></tr>");
                    //        }
                    //        _emailBody.Append("</table>");
                    //        _emailBody.Append("<br/><br/>");
                    //    }
                    //}
                    //else
                    //{
                    //    _emailBody.Append("<b>No mail to send.</b>");
                    //}


                    //while (!MyMailManager.SendGGMail(_smtpInternalAccount, _smtpInternalAuthCode, _toemail, _ccemail, _bccemail, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                    //{
                    //    _eventLog.WriteEntry(_serviceTitle + " - Failed to send STARTED MAIL to GG-Team at [" + dateTimeNow.ToString() + "].", EventLogEntryType.Error);
                    //    System.Threading.Thread.Sleep(_emailIntervalSec * 3000);
                    //}


                    if (IsSendEmailCustomer == "1")
                    {
                        //CC and BCC with customer mail
                        _ccemail = System.Configuration.ConfigurationSettings.AppSettings["CcWithCustomerEmail"].ToString();
                        //_bccemail = System.Configuration.ConfigurationSettings.AppSettings["BccWithCustomerEmail"].ToString();
                        string _bccemailen = "";
                        string _bccemailth = "";
                        if (System.Configuration.ConfigurationSettings.AppSettings["IsCustomerEmailCheck"].ToString() == "1")
                        {
                            _bccemailen = System.Configuration.ConfigurationSettings.AppSettings["BccWithCustomerEmail"].ToString();
                            _bccemailth = System.Configuration.ConfigurationSettings.AppSettings["BccWithCustomerEmail"].ToString();
                        }
                        foreach (DataRow drResrult1 in _dsResult.Tables[0].Rows)
                        {
                            //Before 3 months
                            if (IsLive == "1")
                            {
                                _toemail = drResrult1["Email"].ToString();//"niran.s@comparexpress.com";
                            }

                            if (_toemail.Trim() != "")
                            {
                                string carplateno = "";
                                string policynumber = "";

                                if (drResrult1["PolicyNumber"] != DBNull.Value)
                                {
                                    if (drResrult1["PolicyNumber"].ToString().Trim() != "") policynumber = drResrult1["PolicyNumber"].ToString().Trim();
                                }

                                if (drResrult1["CarPlate"] != DBNull.Value)
                                {
                                    if (drResrult1["CarPlate"].ToString().Trim() != "") carplateno = drResrult1["CarPlate"].ToString().Trim();
                                }

                                _emailBody = new StringBuilder();
                                string domainUrl = "http://www.gluaygluay.com";
                                _emailBody.Append(@"<html>
                                                    <head>
                                                    <meta charset='utf-8'>
                                                    <title>GluayGluay</title>
                                                    <style>
                                                    body {
	                                                    margin-left: 0px;
	                                                    margin-top: 0px;
	                                                    margin-right: 0px;
	                                                    margin-bottom: 0px;
	                                                    font-family: tahoma, Arial, sans-serif;
	                                                    font-size: 14px;
	                                                    color: #4F4356;
	                                                    background-repeat: repeat-x;
	                                                    background-color: #fff;
	                                                    text-decoration: none;
	                                                    font-style: normal;
                                                    }
                                                    a:link  {text-decoration:none; color:#0674DB;}
                                                    hr{
	                                                    display: block; height: 1px;
                                                        border: 0; border-top: 1px solid #ccc;
                                                        margin: 1em 0; padding: 0;
	                                                    }
	                                                    .line-height{
	                                                    line-height:20px;
	                                                    }
                                                    </style>
                                                    </head>
                                                    <body>
                                                    <div class='email-containter'>
                                                    <div class='email-banner'></div>
                                                    <div class='email-text'>
                                                    <table width='800' border='0' align='center' cellpadding='0' cellspacing='0'>
                                                      <tr>
                                                        <td height='653' align='right' valign='bottom' bgcolor='#FFFFFF'>");

                                #region newtemplateEN
                                if (drResrult1["LangOption"].ToString() == "2")
                                {
                                    //English
                                    if (carplateno.Length > 2)
                                    {
                                        _emailTitle = "Renewal Reminder [" + carplateno + "] - GluayGluay.com";
                                    }
                                    else
                                    {
                                        _emailTitle = "Renewal Reminder [" + drResrult1["QuoteRefNo"].ToString() + "] - GluayGluay.com";
                                    }

                                    _emailBody.Append(@"<table width='800' height='100' border='0' cellspacing='0' cellpadding='0'>
                                                          <tr>");
                                    _emailBody.Append("<td><span class='email-banner'><img src='" + domainUrl + "/images/email-header.jpg' width='800' height='100' alt=''/></span></td>");
                                    _emailBody.Append(@"</tr>
                                                        </table>");
                                    _emailBody.Append(@"<table width='800' border='0' align='center' cellpadding='0' cellspacing='0'>
                                                        <tr>
                                                        <td width='530' height='419' align='left' valign='top' bgcolor='#FFFFFF'><p>");

                                    if (drResrult1["Title"] != DBNull.Value && drResrult1["Title"].ToString().Trim().Length > 1)
                                    {
                                        _emailBody.Append("Dear <b>Khun " + drResrult1["CustomerName"].ToString() + "</b>,");
                                    }
                                    else
                                    {
                                        _emailBody.Append("Dear <b>" + drResrult1["CustomerName"].ToString() + "</b>,");
                                    }

                                    DateTime _CoverageStart = Convert.ToDateTime(drResrult1["CoverageStart"]);
                                    DateTime _CTPLStart = Convert.ToDateTime(drResrult1["CTPLStart"]);

                                    _emailBody.Append("<br/><br/>We would like to inform you that your car insurance will be expiring soon. ");
                                    _emailBody.Append("<br/><br/></p>");
                                    _emailBody.Append("<table width='780' border='0' cellspacing='0' cellpadding='0'><tr>");
                                    _emailBody.Append("<td width='423' align='left' valign='top' style='font-style: normal; font-size: 14px;'>");
                                    _emailBody.Append("<table width='420' border='0' cellspacing='0' cellpadding='0'>");
                                    _emailBody.Append("<tr>");
                                    if (policynumber.Length > 0)
                                    {
                                        _emailBody.Append("<td width='188' height='25' align='left'><b>Policy No. / GG Ref no.</b></td>");
                                        _emailBody.Append("<td width='232' align='left'><b>: " + policynumber + " / " + drResrult1["QuoteRefNo"].ToString() + "</b></td>");
                                    }
                                    else
                                    {
                                        _emailBody.Append("<td width='188' height='25' align='left'><b>GG Ref no.</b></td>");
                                        _emailBody.Append("<td width='232' align='left'><b>: " + drResrult1["QuoteRefNo"].ToString() + "</b></td>");
                                    }
                                    _emailBody.Append("</tr>");
                                    _emailBody.Append("<tr>");

                                    if (carplateno.Length > 2)
                                    {
                                        _emailBody.Append("<td height='25' align='left'><b>License Plate</b></td>");
                                        _emailBody.Append("<td height='25' align='left'><b>: " + drResrult1["CarPlate"].ToString() + "</b></td>");
                                    }
                                    _emailBody.Append("</tr>");
                                    _emailBody.Append("<tr>");

                                    _emailBody.Append("<td height='25' align='left'><b>Expiry Date</td>");
                                    _emailBody.Append("<td height='25' align='left'><b>: " + _CoverageStart.AddYears(1).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + "</b></td>");
                                    _emailBody.Append("</tr>");
                                    _emailBody.Append("</table>");

                                    _emailBody.Append("<span style='text-align: justify; font-size: 14px;'><br/>We hope you have enjoyed our service and look forward to being of assistance for this upcoming renewal.</span>");
                                    _emailBody.Append("<br/><br/><span style='font-size: 22px; color: #4F4356;'>Get <b>an early bird</b> discount off your car insurance<br/>when you renew early with us.</span>");

                                    _emailBody.Append("</td>");
                                    _emailBody.Append("<td width='357' valign='top'><img src='" + domainUrl + "/images/renew-chac.jpg' width='357' height='188' alt=''/></td>");
                                    _emailBody.Append("</tr>");
                                    _emailBody.Append("<tr>");

                                    _emailBody.Append(@" <td colspan='2' align='left' style='font-style: normal; font-size: 14px;'><p>
                                                          We are ready to assist at hotline: <strong>02-268-8222</strong> or renewal direct line at <strong>02-646-9110</strong> <span tabindex='0' data-term='goog_235707793'>Monday</span> to Fridays from <span tabindex='0' data-term='goog_235707794'>9am to 6pm</span>. Alternatively, you can email us at <span style='font-weight: bold'>support@gluaygluay.com</span><br>
                                                          <br>
                                                          we are apologize if you already renew your policy.</p>
                  
                                                          </td>
                                                      </tr>
                                                      <tr>
                                                        <td colspan='2' align='left' style='font-style: normal; font-size: 14px;'>With Best Regards,</td>
                                                      </tr>
              
                                                    </table>
                                                    <hr>
                                                    <p><b>GluayGluay Customer Service Team</b><br>
                                                      CompareXpress Insurance Broker (Thailand) Ltd.<br>
                                                     OIC  License number ว00016/2553  <br>
                                                      42nd Floor, United Center Building,<br>
                                                      323 Silom Road, Bangrak,<br>
                                                      Bangkok 10500</p>
                                                    <p>Tel : 02-268-8222 <br>
                                                      Fax : 02-266-6339 <br>
                                                  <a href='mailto:support@gluaygluay.com'>support@gluaygluay.com</a></p></td>
                                                </tr>
                                              </table>
                                              <hr>
      
                                              <table width='300' border='0' align='center' cellpadding='0' cellspacing='0'>
                                                <tr>");
                                    _emailBody.Append("<td width='44' style='text-align: center'><a href='https://www.facebook.com/GGmotor'><img src='" + domainUrl + "/images/icons/fb-big.png' width='44' height='42' alt=''/></a></td>");
                                    _emailBody.Append("<td width='44' style='text-align: center'><a href='https://plus.google.com/+Gluaygluay/posts'><img src='" + domainUrl + "/images/icons/g-plus-big.png' width='44' height='42' alt=''/></a></td>");
                                    _emailBody.Append("<td width='44' style='text-align: center'><a href='https://www.youtube.com/channel/UCZ4gMtlXymBHxvcXO2cR1vA'><img src='" + domainUrl + "/images/icons/youtube-big.png' width='44' height='42' alt=''/></a></td>");
                                    _emailBody.Append("<td width='44' style='text-align: center'><a href='http://iconosquare.com/ggmotor'><img src='" + domainUrl + "/images/icons/instagram-big.png' width='44' height='42' alt=''/></a></td>");
                                    _emailBody.Append("<td width='43' style='text-align: center'><a href='https://www.linkedin.com/company/-com-gluaygluay-com-'><img src='" + domainUrl + "/images/icons/linkedin-big.png' width='44' height='42' alt=''/></a></td>");
                                    _emailBody.Append(@" </tr>
                                              </table>");
                                    _emailBody.Append("<table width='800' height='100' border='0' cellspacing='0' cellpadding='0' style=\"background-image: url('" + domainUrl + "/images/email-footer.jpg');\">");
                                    _emailBody.Append(@"<tr>
                                                  <td><p style='text-align: center'><br><br>
                                                    © ");
                                    _emailBody.Append(DateTime.Now.ToString("yyyy", new System.Globalization.CultureInfo("en-US")));
                                    _emailBody.Append(@" gluaygluay.com. All rights reserved.<br>
                                                      This is a computer-generated email. Please do not reply to this email. </p></td>
                                                </tr>
                                              </table></td>
                                          </tr>
                                        </table>
                                        </body>
                                        </html>");

                                #endregion
                                #region old template EN
                                //if (drResrult1["LangOption"].ToString() == "2")
                                //{
                                //    //English
                                //    if (carplateno.Length > 2)
                                //    {
                                //        //_emailTitle = "Renewal Reminder - Your motor policy for license plate " + carplateno + " is expiring soon. Renew today!";
                                //        _emailTitle = "Renewal Reminder [" + carplateno + "] - GluayGluay.com";
                                //    }
                                //    else
                                //    {
                                //        //_emailTitle = "Renewal Reminder: Ref No." + drResrult1["QuoteRefNo"].ToString() + " - Your motor policy is expiring soon. Renew today!";
                                //        _emailTitle = "Renewal Reminder [" + drResrult1["QuoteRefNo"].ToString() + "] - GluayGluay.com";
                                //    }

                                //    if (drResrult1["Title"] != DBNull.Value && drResrult1["Title"].ToString().Trim().Length > 1)
                                //    {
                                //        //[YUI] @27/08/2013 remove title " + drResrult1["Title"].ToString() + "
                                //        _emailBody.Append("Dear <b>Khun " + drResrult1["CustomerName"].ToString() + "</b>,");
                                //    }
                                //    else
                                //    {
                                //        _emailBody.Append("Dear <b>" + drResrult1["CustomerName"].ToString() + "</b>,");
                                //    }

                                //    DateTime _CoverageStart = Convert.ToDateTime(drResrult1["CoverageStart"]);
                                //    DateTime _CTPLStart = Convert.ToDateTime(drResrult1["CTPLStart"]);

                                //    _emailBody.Append("<br/><br/>We would like to inform you that your car insurance will be expiring soon. ");
                                //    //_emailBody.Append("<br/><br/>Your car insurance is going to expire on " + _CoverageStart.AddYears(1).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + ". Do you want to re-new it with GluayGluay.com? ");
                                //    _emailBody.Append("<br/><br/><table border='0' style=\"padding-right:5\">");
                                //    _emailBody.Append("<tr>");
                                //    if (policynumber.Length > 0)
                                //    {
                                //        _emailBody.Append("<td><b>Policy No. / GG Ref no.</b></td>");
                                //        _emailBody.Append("<td><b>: " + policynumber + " / " + drResrult1["QuoteRefNo"].ToString() + "</b></td>");
                                //    }
                                //    else
                                //    {
                                //        _emailBody.Append("<td><b>GG Ref no.</b></td>");
                                //        _emailBody.Append("<td><b>: " + drResrult1["QuoteRefNo"].ToString() + "</b></td>");
                                //    }
                                //    //_emailBody.Append("<td>Car Make / Model</td>");
                                //    //_emailBody.Append("<td>: " + drResrult1["CarModel"].ToString() + "</td>");
                                //    _emailBody.Append("</tr>");
                                //    _emailBody.Append("<tr>");

                                //    if (carplateno.Length > 2)
                                //    {
                                //        _emailBody.Append("<td><b>License Plate</b></td>");
                                //        _emailBody.Append("<td><b>: " + drResrult1["CarPlate"].ToString() + "</b></td>");
                                //    }
                                //    //_emailBody.Append("<td>Year</td>");
                                //    //_emailBody.Append("<td>: " + drResrult1["CarYear"].ToString() + "</td>");
                                //    _emailBody.Append("</tr>");
                                //    _emailBody.Append("<tr>");
                                //    _emailBody.Append("<td><b>Expiry Date</td>");
                                //    _emailBody.Append("<td><b>: " + _CoverageStart.AddYears(1).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + "</b></td>");
                                //    //_emailBody.Append("<td>Named Driver</td>");
                                //    //_emailBody.Append("<td>: " + (int.Parse(drResrult1["NoOfDriver"].ToString()) > 0 ? "Yes" : "No") + "</td>");
                                //    //_emailBody.Append("<td>Include CTPL</td>");
                                //    //_emailBody.Append("<td>: " + (drResrult1["IsCTPL"].ToString().ToLower() == "true" ? "Yes" : "No") + "</td>");
                                //    _emailBody.Append("</tr>");
                                //    _emailBody.Append("</table>");
                                //    _emailBody.Append("<br/><br/>We hope you have enjoyed our service and look forward to being of assistance for this upcoming renewal.");
                                //    _emailBody.Append("<br/><br/><span style=\"font-size:x-large\">Get <b>an early bird</b> discount off your car insurance<br/>when you renew early with us.</span>");
                                //    //_emailBody.Append("<br/>Policy Details");
                                //    //_emailBody.Append("<br/><table border=\"1\" cellpadding=\"5\" cellspacing=\"0\" style=\"border-color:Black;border-style:solid;border-width:1px\">");
                                //    //_emailBody.Append("<tr>");
                                //    //_emailBody.Append("<td style=\"width:150px;background-color:#ccc;border-color:Black;\">Policy Number</td>");
                                //    //_emailBody.Append("<td style=\"border-color:Black;\">" + drResrult1["PolicyNumber"].ToString() + "</td>");
                                //    //_emailBody.Append("</tr>");
                                //    //_emailBody.Append("<tr>");
                                //    //_emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">Insurance Company</td>");
                                //    //_emailBody.Append("<td style=\"border-color:Black;\">" + drResrult1["CompanyNameDescription"].ToString() + "</td>");
                                //    //_emailBody.Append("</tr>");
                                //    //_emailBody.Append("<tr>");
                                //    //_emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">Insurance Type</td>");
                                //    //_emailBody.Append("<td style=\"border-color:Black;\">" + GetClassNameEN(drResrult1["InsuranceType"].ToString()) + "</td>");
                                //    //_emailBody.Append("</tr>");
                                //    //_emailBody.Append("<tr>");
                                //    //_emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">Package Name</td>");
                                //    //_emailBody.Append("<td style=\"border-color:Black;\">" + drResrult1["Package"].ToString() + "</td>");
                                //    //_emailBody.Append("</tr>");
                                //    //_emailBody.Append("<tr>");
                                //    //_emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">Capital Insured</td>");
                                //    //_emailBody.Append("<td style=\"border-color:Black;\">" + Convert.ToDecimal(drResrult1["SumInsured"]).ToString("#,##0.00") + "</td>");
                                //    //_emailBody.Append("</tr>");
                                //    //_emailBody.Append("<tr>");
                                //    //_emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">Premium</td>");
                                //    //_emailBody.Append("<td style=\"border-color:Black;\">" + Convert.ToDecimal(drResrult1["Premium"]).ToString("#,##0.00") + "</td>");
                                //    //_emailBody.Append("</tr>");
                                //    //_emailBody.Append("<tr>");
                                //    //_emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">Policy Start Date</td>");
                                //    //_emailBody.Append("<td style=\"border-color:Black;\">" + _CoverageStart.ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + "</td>");
                                //    //_emailBody.Append("</tr>");
                                //    //if (drResrult1["IsCTPL"].ToString().ToLower() == "true")
                                //    //{
                                //    //    _emailBody.Append("<tr>");
                                //    //    _emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">CTPL Amount</td>");
                                //    //    _emailBody.Append("<td style=\"border-color:Black;\">" + Convert.ToDecimal(drResrult1["CTPL"]).ToString("#,##0.00") + "</td>");
                                //    //    _emailBody.Append("</tr>");
                                //    //    _emailBody.Append("<tr>");
                                //    //    _emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">CTPL Start Date</td>");
                                //    //    _emailBody.Append("<td style=\"border-color:Black;\">" + _CTPLStart.ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + "</td>");
                                //    //    _emailBody.Append("</tr>");
                                //    //}
                                //    //_emailBody.Append("</table>");

                                //    _emailBody.Append("<br/><br/>We are ready to assist at hotline: <strong>02-268-8222</strong> or renewal direct line at <strong>02-646-9110</strong> Monday to Fridays from 9am to 6pm. Alternatively, you can email us at <strong>support@gluaygluay.com.</strong>");
                                //    _emailBody.Append("<br/><br/>we are apologize if you already renew your policy.");
                                //    _emailBody.Append("<br/><br/>Best regards,");
                                //    _emailBody.Append("<br/><br/>Support GluayGluay.com");
                                //    _emailBody.Append("<br/><br/>CompareXpress Insurance Broker (Thailand) Ltd. ");
                                //    _emailBody.Append("<br/>OIC Licensed Non-Life Insurance Broker: ว00016/2553");
                                //    _emailBody.Append("<br/>42nd Floor, United Center Building,323 Silom Road, Bangrak, Bangkok 10500");
                                //    _emailBody.Append("<br/>W: <a href='http://www.GluayGluay.com'>www.GluayGluay.com</a> | T: 02-268 8222 | E: support@gluaygluay.com ");
                                //    _emailBody.Append("<br/>Follow us: <a href='http://www.facebook.com/GGmotor'>www.facebook.com/GGmotor</a> | Instagram: #GGmotor ");
                                //    _emailBody.Append("<br/><br/>");

                                //    _emailBody.Append("<a href='http://www.GluayGluay.com'><img src='http://www.gluaygluay.com/images/public/logo/gluaygluay-plakat_export.jpg' alt='GluayGluay.com'/></a>");
                                //    //_emailBody.Append("<br/>GluayGluay.com.<br/>");
                                //    //_emailBody.Append("http://www.GluayGluay.com<br/><br/>");
#endregion
                                    bool _emailSend = false;
                                    while (!_emailSend)
                                    {
                                        if (MyMailManager.SendGGMail(_smtpAccount, _smtpAuthCode, _toemail, _ccemail, _bccemailen, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                                        {
                                            _mailThreeSendCount++;
                                            _emailSend = true;
                                            _bccemailen = "";
                                        }
                                        else
                                        {
                                            _eventLog.WriteEntry(_serviceTitle + " Failed sending to " + _toemail + " at [" + dateTimeNow.ToString() + "].", EventLogEntryType.Error);
                                            System.Threading.Thread.Sleep(_emailIntervalSec * 3000);
                                        }
                                    }

                                    System.Threading.Thread.Sleep(_emailIntervalSec * 1000);
                                }
                                else if (drResrult1["LangOption"].ToString() == "1")
                                {
                                    #region newtemplateTH
                                    //English
                                    if (carplateno.Length > 2)
                                    {
                                        _emailTitle = "แจ้งเตือนการต่ออายุประกัน ทะเบียน [" + carplateno + "] - GluayGluay.com";
                                    }
                                    else
                                    {
                                        _emailTitle = "แจ้งเตือนการต่ออายุประกัน ทะเบียน [" + drResrult1["QuoteRefNo"].ToString() + "] - GluayGluay.com";
                                    }

                                    _emailBody.Append(@"<table width='800' height='100' border='0' cellspacing='0' cellpadding='0'>
                                                          <tr>");
                                    _emailBody.Append("<td><span class='email-banner'><img src='" + domainUrl + "/images/email-header.jpg' width='800' height='100' alt=''/></span></td>");
                                    _emailBody.Append(@"</tr>
                                                        </table>");
                                    _emailBody.Append(@"<table width='800' border='0' align='center' cellpadding='0' cellspacing='0'>
                                                        <tr>
                                                        <td width='530' height='419' align='left' valign='top' bgcolor='#FFFFFF'><p>");

                                    if (drResrult1["Title"] != DBNull.Value && drResrult1["Title"].ToString().Trim().Length > 1)
                                    {
                                        _emailBody.Append("เรียน <b>คุณ " + drResrult1["CustomerName"].ToString() + "</b>,");
                                    }
                                    else
                                    {
                                        _emailBody.Append("เรียน <b>" + drResrult1["CustomerName"].ToString() + "</b>,");
                                    }

                                    DateTime _CoverageStart = Convert.ToDateTime(drResrult1["CoverageStart"]);
                                    DateTime _CTPLStart = Convert.ToDateTime(drResrult1["CTPLStart"]);

                                    _emailBody.Append("<br/><br/>เราต้องการเรียนแจ้งให้คุณทราบว่ากรมธรรม์ประกันภัยรถยนต์ของคุณใกล้จะหมดอายุเร็ว ๆ นี้  ");
                                    _emailBody.Append("<br/><br/></p>");
                                    _emailBody.Append("<table width='780' border='0' cellspacing='0' cellpadding='0'><tr>");
                                    _emailBody.Append("<td width='423' align='left' valign='top' style='font-style: normal; font-size: 14px;'>");
                                    _emailBody.Append("<table width='420' border='0' cellspacing='0' cellpadding='0'>");
                                    _emailBody.Append("<tr>");
                                    if (policynumber.Length > 0)
                                    {
                                        _emailBody.Append("<td width='188' height='25' align='left'><b>หมายเลขกรมธรรม์ / เลขอ้างอิง</b></td>");
                                        _emailBody.Append("<td width='232' align='left'><b>: " + policynumber + " / " + drResrult1["QuoteRefNo"].ToString() + "</b></td>");
                                    }
                                    else
                                    {
                                        _emailBody.Append("<td width='188' height='25' align='left'><b>เลขอ้างอิง</b></td>");
                                        _emailBody.Append("<td width='232' align='left'><b>: " + drResrult1["QuoteRefNo"].ToString() + "</b></td>");
                                    }
                                    _emailBody.Append("</tr>");
                                    _emailBody.Append("<tr>");

                                    if (carplateno.Length > 2)
                                    {
                                        _emailBody.Append("<td height='25' align='left'><b>ทะเบียนรถ</b></td>");
                                        _emailBody.Append("<td height='25' align='left'><b>: " + drResrult1["CarPlate"].ToString() + "</b></td>");
                                    }
                                    _emailBody.Append("</tr>");
                                    _emailBody.Append("<tr>");

                                    _emailBody.Append("<td height='25' align='left'><b>วันที่หมดอายุ</td>");
                                    _emailBody.Append("<td height='25' align='left'><b>: " + _CoverageStart.AddYears(1).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + "</b></td>");
                                    _emailBody.Append("</tr>");
                                    _emailBody.Append("</table>");

                                    _emailBody.Append("<span style='text-align: justify; font-size: 14px;'><br/>เราหวังว่าคุณจะพึงพอใจในการให้บริการของเราและหวังเป็นอย่างยิ่งว่าจะได้ช่วยเหลือคุณในการต่ออายุประกันภัยรถยนตร์สำหรับปีนี้และต่อๆไป </span>");
                                    _emailBody.Append("<br/><br/><span style='font-size: 22px; color: #4F4356;'>รับ <b>ส่วนลดพิเศษเพิ่ม</b> เมื่อคุณต่ออายุประกันภัยรถยนต์กับเราล่วงหน้า</span>");


                                    _emailBody.Append("</td>");
                                    _emailBody.Append("<td width='357' valign='top'><img src='" + domainUrl + "/images/renew-chac.jpg' width='357' height='188' alt=''/></td>");
                                    _emailBody.Append("</tr>");
                                    _emailBody.Append("<tr>");

                                    _emailBody.Append(@" <td colspan='2' align='left' style='font-style: normal; font-size: 14px;'><p>
                                                   เราพร้อมที่จะช่วยเหลือและให้บริการคุณ โปรดติดต่อที่เบอร์กลาง <strong>02-268-8222</strong> หรือ ติดต่อเจ้าหน้าที่งานต่ออายุโดยตรงที่เบอร์ <strong>02-646-9110</strong> วันจันทร์ ถึง วันศุกร์ เวลา 9.00 – 18.00 หรือส่งอีเมล์มาหาเราที่  <strong>support@gluaygluay.com.</strong><br>
                                                          <br>
                                                          ขออภัยหากคุณได้ทำการต่ออายุประกันแล้ว</p>
                  
                                                          </td>
                                                      </tr>
                                                      <tr>
                                                        <td colspan='2' align='left' style='font-style: normal; font-size: 14px;'>ด้วยความนับถือ,</td>
                                                      </tr>
                                                    </table>
                                                    <hr>");
                                    _emailBody.Append("<span style=\"color:red; font-size:25px;\">โปรดระวังมิจฉาชีพแอบอ้างเป็นโบรกเกอร์กล้วยๆ<br />หรือแอบอ้างเป็นศูนย์รถหรือได้เบอร์มาจากบริษัทประกันภัย</span><br />");
                                    _emailBody.Append("เจ้าหน้าที่ของเราจะติดต่อท่านด้วยหมายเลข 02 646 9101 - 16 เท่านั้น<br />");
                                    _emailBody.Append("สามารถตรวจสอบข้อมูลเพิ่มเติมได้ที่");
                                    _emailBody.Append("<a  style=\"color:rgb(59,89,152);text-decoration:none\" href=\"http://www.gluaygluay.com\">www.GluayGluay.com</a>");
                                    _emailBody.Append("<br />หรือโทร <a href=\"tel:022688222\"><strong>0-2268-8222</strong></a><br />");
                                    _emailBody.Append(@"<p><b>ฝ่ายประสานงาน GluayGluay.com</b><br>
                                                      บริษัท คอมแพร์เอ็กซ์เพรส นายหน้าประกันภัย (ประเทศไทย) จำกัด<br>
                                                    เลขทะเบียนนายหน้าประกันวินาศภัยนิติบุคคล เลขที่ ว00016/2553  <br>
                                                      ชั้น 42 อาคารยูไนเต็ด เซ็นเตอร์,<br>
                                                      เลขที่ 323 ถนนสีลม แขวงสีลม เขตบางรัก,<br>
                                                      กรุงเทพฯ 10500</p>
                                                    <p>Tel : 02-268-8222 <br>
                                                      Fax : 02-266-6339 <br>
                                                  <a href='mailto:support@gluaygluay.com'>support@gluaygluay.com</a></p></td>
                                                </tr>
                                              </table>
                                              <hr>
      
                                              <table width='300' border='0' align='center' cellpadding='0' cellspacing='0'>
                                                <tr>");
                                    _emailBody.Append("<td width='44' style='text-align: center'><a href='https://www.facebook.com/GGmotor'><img src='" + domainUrl + "/images/icons/fb-big.png' width='44' height='42' alt=''/></a></td>");
                                    _emailBody.Append("<td width='44' style='text-align: center'><a href='https://plus.google.com/+Gluaygluay/posts'><img src='" + domainUrl + "/images/icons/g-plus-big.png' width='44' height='42' alt=''/></a></td>");
                                    _emailBody.Append("<td width='44' style='text-align: center'><a href='https://www.youtube.com/channel/UCZ4gMtlXymBHxvcXO2cR1vA'><img src='" + domainUrl + "/images/icons/youtube-big.png' width='44' height='42' alt=''/></a></td>");
                                    _emailBody.Append("<td width='44' style='text-align: center'><a href='http://iconosquare.com/ggmotor'><img src='" + domainUrl + "/images/icons/instagram-big.png' width='44' height='42' alt=''/></a></td>");
                                    _emailBody.Append("<td width='43' style='text-align: center'><a href='https://www.linkedin.com/company/-com-gluaygluay-com-'><img src='" + domainUrl + "/images/icons/linkedin-big.png' width='44' height='42' alt=''/></a></td>");
                                    _emailBody.Append(@" </tr>
                                              </table>");
                                    _emailBody.Append("<table width='800' height='100' border='0' cellspacing='0' cellpadding='0' style=\"background-image: url('" + domainUrl + "/images/email-footer.jpg');\">");
                                    _emailBody.Append(@"<tr>                                             
                                                  <td><p style='text-align: center'><br><br>
                                                    © ");
                                    _emailBody.Append(DateTime.Now.ToString("yyyy", new System.Globalization.CultureInfo("en-US")));
                                    _emailBody.Append(@" gluaygluay.com. All rights reserved.<br>
                                                      อีเมลนี้ถูกส่งอัตโนมัติจากระบบ. กรุณาอย่าตอบกลับ </p></td>
                                                </tr>
                                              </table></td>
                                          </tr>
                                        </table>
                                        </body>
                                        </html>");
#endregion
                                    #region oldTH
                                    ////Thai
                                    //if (carplateno.Length > 2)
                                    //{
                                    //    //_emailTitle = "แจ้งเตือนการต่ออายุ – ประกันภัยรถยนตร์ของคุณ ทะเบียน " + carplateno + " จะหมดอายุเร็วๆนี้ ต่ออายุได้เลยวันนี้";
                                    //    _emailTitle = "แจ้งเตือนการต่ออายุประกัน ทะเบียน " + carplateno + " - GluayGluay.com";
                                    //}
                                    //else
                                    //{
                                    //    //_emailTitle = "แจ้งเตือนการต่ออายุ - เลขอ้างอิง " + drResrult1["QuoteRefNo"].ToString() + " – ประกันภัยรถยนตร์ของคุณจะหมดอายุเร็วๆนี้ ต่ออายุได้เลยวันนี้";
                                    //    _emailTitle = "แจ้งเตือนการต่ออายุประกัน เลขที่อ้างอิง " + drResrult1["QuoteRefNo"].ToString() + " - GluayGluay.com";
                                    //}

                                    //if (drResrult1["Title"] != DBNull.Value && drResrult1["Title"].ToString().Trim().Length > 1)
                                    //{
                                    //    //[YUI] @19/08/2013 remove title " + drResrult1["Title"].ToString() + "
                                    //    _emailBody.Append("เรียน <b>คุณ " + drResrult1["CustomerName"].ToString() + "</b>,");
                                    //}
                                    //else
                                    //{
                                    //    _emailBody.Append("เรียน <b>" + drResrult1["CustomerName"].ToString() + "</b>,");
                                    //}

                                    //DateTime _CoverageStart = Convert.ToDateTime(drResrult1["CoverageStart"]);
                                    //DateTime _CTPLStart = Convert.ToDateTime(drResrult1["CTPLStart"]);

                                    //_emailBody.Append("<br/><br/>เราต้องการเรียนแจ้งให้คุณทราบว่ากรมธรรม์ประกันภัยรถยนต์ของคุณใกล้จะหมดอายุเร็ว ๆ นี้ ");
                                    //_emailBody.Append("<br/><br/><table border='0' style=\"padding-right:5\">");
                                    //_emailBody.Append("<tr>");
                                    //if (policynumber.Length > 0)
                                    //{
                                    //    _emailBody.Append("<td><b>หมายเลขกรมธรรม์ / เลขอ้างอิง GluayGluay.com</b></td>");
                                    //    _emailBody.Append("<td><b>: " + policynumber + " / " + drResrult1["QuoteRefNo"].ToString() + "</b></td>");
                                    //}
                                    //else
                                    //{
                                    //    _emailBody.Append("<td><b>เลขอ้างอิง GluayGluay.com</b></td>");
                                    //    _emailBody.Append("<td><b>: " + drResrult1["QuoteRefNo"].ToString() + "</b></td>");
                                    //}
                                    //_emailBody.Append("</tr>");
                                    //_emailBody.Append("<tr>");

                                    //if (carplateno.Length > 2)
                                    //{
                                    //    _emailBody.Append("<td><b>ทะเบียนรถ </b></td>");
                                    //    _emailBody.Append("<td><b>: " + drResrult1["CarPlate"].ToString() + "</b></td>");
                                    //}
                                    //_emailBody.Append("</tr>");
                                    //_emailBody.Append("<tr>");
                                    //_emailBody.Append("<td><b>วันที่หมดอายุ</td>");
                                    //_emailBody.Append("<td><b>: " + _CoverageStart.AddYears(1).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + "</b></td>");
                                    //_emailBody.Append("</tr>");
                                    //_emailBody.Append("</table>");
                                    //_emailBody.Append("<br/><br/>เราหวังว่าคุณจะพึงพอใจในการให้บริการของเราและหวังเป็นอย่างยิ่งว่าจะได้ช่วยเหลือคุณในการต่ออายุประกันภัยรถยนตร์สำหรับปีนี้และต่อๆไป");
                                    //_emailBody.Append("<br/><br/><span style=\"font-size:x-large\">รับ <b>ส่วนลดพิเศษเพิ่ม</b> เมื่อคุณต่ออายุประกันภัยรถยนต์กับเราล่วงหน้า</span>");

                                    //_emailBody.Append("<br/><br/>เราพร้อมที่จะช่วยเหลือและให้บริการคุณ โปรดติดต่อที่เบอร์กลาง <strong>02-268-8222</strong> หรือ ติดต่อเจ้าหน้าที่งานต่ออายุโดยตรงที่เบอร์ <strong>02-646-9110</strong> วันจันทร์ ถึง วันศุกร์ เวลา 9.00 – 18.00 หรือส่งอีเมล์มาหาเราที่  <strong>support@gluaygluay.com.</strong>");
                                    //_emailBody.Append("<br/><br/>ขออภัยหากคุณได้ทำการต่ออายุประกันแล้ว");
                                    //_emailBody.Append("<br/><br/>ด้วยความนับถือ,");
                                    //_emailBody.Append("<br/><br/>ทีมงาน GluayGluay.com");
                                    //_emailBody.Append("<br/><br/>บริษัท คอมแพร์เอ็กซ์เพรส นายหน้าประกันภัย (ประเทศไทย) จำกัด");
                                    //_emailBody.Append("<br/>เลขทะเบียนนายหน้าประกันวินาศภัยนิติบุคคล เลขที่ ว00016/2553");
                                    //_emailBody.Append("<br/>ชั้น 42 อาคารยูไนเต็ด เซ็นเตอร์ เลขที่ 323 ถนนสีลม แขวงสีลม เขตบางรัก กรุงเทพฯ 10500");
                                    //_emailBody.Append("<br/>เว็บไซต์: <a href='http://www.GluayGluay.com'>www.GluayGluay.com</a> | โทร.: 02-268 8222 | อีเมล์: support@gluaygluay.com ");
                                    //_emailBody.Append("<br/>Follow us: <a href='http://www.facebook.com/GGmotor'>www.facebook.com/GGmotor</a> | Instagram: #GGmotor ");
                                    //_emailBody.Append("<br/><br/>");

                                    //_emailBody.Append("<a href='http://www.GluayGluay.com'><img src='http://www.gluaygluay.com/images/public/logo/gluaygluay-plakat_export.jpg' alt='GluayGluay.com'/></a>");
                                    
                                    #endregion
                                    bool _emailSend = false;

                                    while (!_emailSend)
                                    {
                                        if (MyMailManager.SendGGMail(_smtpAccount, _smtpAuthCode, _toemail, _ccemail, _bccemailth, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                                        {
                                            _mailThreeSendCount++;
                                            _emailSend = true;
                                            _bccemailth = "";
                                        }
                                        else
                                        {
                                            _eventLog.WriteEntry(_serviceTitle + " Failed sending to " + _toemail + " at [" + dateTimeNow.ToString() + "].", EventLogEntryType.Error);
                                            System.Threading.Thread.Sleep(_emailIntervalSec * 3000);
                                        }
                                    }

                                    System.Threading.Thread.Sleep(_emailIntervalSec * 1000);
                                }

                            }
                        }
                    }

                    if (_dsResult.Tables[0].Rows.Count > 0)
                    {
                        _eventLog.WriteEntry(_serviceTitle + " Finished at [" + dateTimeNow.ToString("dd/MMM/yyyy hh:mm:ss tt") + "].", EventLogEntryType.Information);
                        //9/12/2556 save to tblWindowServiceLog
                        try
                        {
                            _sqlCmd = new SqlCommand();
                            _sqlCmd.Connection = _sqlConn;
                            _sqlCmd.CommandType = CommandType.StoredProcedure;
                            _sqlCmd.CommandText = "[sprocWindowServiceSaveLog]";
                            _sqlCmd.Parameters.AddWithValue("@ServiceName", "GG_CRM_Renewal_Reminder_MailService");
                            _sqlCmd.Parameters.AddWithValue("@Subject", _serviceTitle + " was finished at " + MyMailManager.GetUTCDateTime().ToString("dd/MM/yyyy hh:mm:ss"));
                            _sqlCmd.Parameters.AddWithValue("@EmailCount", _mailThreeSendCount);
                            _sqlCmd.Parameters.AddWithValue("@ServiceStatus", 1);
                            _sqlCmd.Parameters.AddWithValue("@Status", 1);
                            _sqlConn.Open();
                            _sqlCmd.ExecuteNonQuery();
                            _sqlConn.Close();
                        }
                        catch { }

                        //_toemail = System.Configuration.ConfigurationSettings.AppSettings["ToEmail"].ToString();
                        //_ccemail = System.Configuration.ConfigurationSettings.AppSettings["CcEmail"].ToString();
                        //_bccemail = System.Configuration.ConfigurationSettings.AppSettings["BccEmail"].ToString();
                        //_emailTitle = "<Finished> " + _serviceTitle + " - " + dateTimeNow.ToString("dd-MMM-yyyy hh:mm tt");
                        //_emailBody = new StringBuilder();
                        //_emailBody.Append("Dear All,<br/><br/>");
                        //_emailBody.Append(_serviceTitle + " was finished at " + dateTimeNow.ToString("dd/MMM/yyyy hh:mm:ss tt") + "<br/><br/>");

                        //if (IsSendEmailCustomer == "1")
                        //{
                        //    _emailBody.Append("No. of 75 days successfully sent mail : <b>" + _mailThreeSendCount.ToString() + "</b><br/><br/>");
                        //}
                        //else
                        //{
                        //    _emailBody.Append("Is send Email Customer : <b>" + IsSendEmailCustomer + "</b><br/><br/>");
                        //}

                        //System.Threading.Thread.Sleep(_emailIntervalSec * 1000);

                        //while (!MyMailManager.SendGGMail(_smtpInternalAccount, _smtpInternalAuthCode, _toemail, _ccemail, _bccemail, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                        //{
                        //    _eventLog.WriteEntry(_serviceTitle + " - Failed to send FINISH MAIL to GG-Team at [" + dateTimeNow.ToString() + "].", EventLogEntryType.Error);
                        //    System.Threading.Thread.Sleep(_emailIntervalSec * 3000);
                        //}
                    }
                }

                #region MailBody

                //if (_dsResult != null && _dsResult.Tables.Count == 2)
                //{
                //    _step = "Step 2.1";
                //    int _mailThreeSendCount = 0;
                //    int _mailOneSendCount = 0;
                //    if (_dsResult.Tables[0].Rows.Count > 0 || _dsResult.Tables[1].Rows.Count > 0)
                //    {
                //        _eventLog.WriteEntry(_serviceTitle + " Started at [" + dateTimeNow.ToString("dd/MMM/yyyy hh:mm:ss") + "].", EventLogEntryType.Information);
                //    }
                //    else
                //    {
                //        _eventLog.WriteEntry(_serviceTitle + " No Mail to send. [" + dateTimeNow.ToString() + "].", EventLogEntryType.Information);
                //    }

                //    _emailBody = new StringBuilder();
                //    _emailTitle = "<Started> " + _serviceTitle + " - " + dateTimeNow.ToString("dd-MMM-yyyy hh:mm tt");
                //    _emailBody.Append("Dear All,<br/><br/>");
                //    _emailBody.Append(_serviceTitle + " was started at " + dateTimeNow.ToString("dd/MMM/yyyy hh:mm:ss") + "<br/><br/>");

                //    _emailBody.Append("No. of days to batch : <b>" + NoOfDays.ToString() + "</b> days.<br/><br/>");

                //    if (_dsResult.Tables[0].Rows.Count > 0 || _dsResult.Tables[1].Rows.Count > 0)
                //    {
                //        _emailBody.Append("No. of 90 days earlier mail : <b>" + _dsResult.Tables[0].Rows.Count + "</b><br/>");
                //        _emailBody.Append("No. of 30 days earlier mail : <b>" + _dsResult.Tables[1].Rows.Count + "</b><br/><br/>");

                //        _emailBody.Append("<b>Please check following customer list details as your reference.</b><br/><br/>");

                //        if (_dsResult.Tables[0].Rows.Count > 0)
                //        {
                //            _emailBody.Append("<table border='1'>");
                //            _emailBody.Append("<caption style='color:blue'><b>For 3 months earlier</b></caption>");
                //            _emailBody.Append("<tr bgcolor='lightgray'><th>No.</th><th>Quote.Ref</th><th>Name</th><th>Car Plate</th><th>Policy No.</th><th>Coverage End Date</th></tr>");
                //            for (int i = 0; i < _dsResult.Tables[0].Rows.Count; i++)
                //            {
                //                _emailBody.Append("<tr " + ((i % 2 == 0) ? "bgcolor='#ffffcc'" : "") + "><td>" + (i + 1).ToString() + "</td><td>" + _dsResult.Tables[0].Rows[i]["QuoteRefNo"] + "</td><td>" + _dsResult.Tables[0].Rows[i]["Name"] + "</th><td>" + _dsResult.Tables[0].Rows[i]["CarPlate"] + "</td><td>" + _dsResult.Tables[0].Rows[i]["PolicyNumber"] + "</td><td>" + Convert.ToDateTime(_dsResult.Tables[0].Rows[i]["CoverageStart"].ToString()).AddYears(1).ToString("dd-MMM-yyyy") + "</td></tr>");
                //            }
                //            _emailBody.Append("</table>");
                //            _emailBody.Append("<br/><br/>");
                //        }

                //        if (_dsResult.Tables[1].Rows.Count > 0)
                //        {
                //            _emailBody.Append("<table border='1'>");
                //            _emailBody.Append("<caption style='color:blue'><b>For 1 month earlier</b></caption>");
                //            _emailBody.Append("<tr bgcolor='lightgray'><th>No.</th><th>Quote.Ref</th><th>Name</th><th>Car Plate</th><th>Policy No.</th><th>Coverage End Date</th></tr>");

                //            for (int i = 0; i < _dsResult.Tables[1].Rows.Count; i++)
                //            {
                //                _emailBody.Append("<tr " + ((i % 2 == 0) ? "bgcolor='#ffffcc'" : "") + "><td>" + (i + 1).ToString() + "</td><td>" + _dsResult.Tables[1].Rows[i]["QuoteRefNo"] + "</td><td>" + _dsResult.Tables[1].Rows[i]["Name"] + "</th><td>" + _dsResult.Tables[1].Rows[i]["CarPlate"] + "</td><td>" + _dsResult.Tables[1].Rows[i]["PolicyNumber"] + "</td><td>" + Convert.ToDateTime(_dsResult.Tables[1].Rows[i]["CoverageStart"].ToString()).AddYears(1).ToString("dd-MMM-yyyy") + "</td></tr>");
                //            }
                //            _emailBody.Append("</table>");
                //        }
                //    }
                //    else
                //    {
                //        _emailBody.Append("<b>No mail to send.</b>");
                //    }


                //    while (!MyMailManager.SendGGMail(_smtpInternalAccount, _smtpInternalAuthCode, _toemail, _ccemail, _bccemail, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                //    {
                //        _eventLog.WriteEntry(_serviceTitle + " - Failed to send STARTED MAIL to GG-Team at [" + dateTimeNow.ToString() + "].", EventLogEntryType.Error);
                //        System.Threading.Thread.Sleep(_emailIntervalSec * 3000);
                //    }


                //    if (IsSendEmailCustomer == "1")
                //    {
                //        //CC and BCC with customer mail
                //        _ccemail = System.Configuration.ConfigurationSettings.AppSettings["CcWithCustomerEmail"].ToString();
                //        _bccemail = System.Configuration.ConfigurationSettings.AppSettings["BccWithCustomerEmail"].ToString();

                //        foreach (DataRow drResrult1 in _dsResult.Tables[0].Rows)
                //        {
                //            //Before 3 months
                //            if (IsLive == "1")
                //            {
                //                _toemail = drResrult1["Email"].ToString();
                //            }

                //            if (_toemail.Trim() != "")
                //            {
                //                string carplateno = "";
                //                string policynumber = "";

                //                if (drResrult1["PolicyNumber"] != DBNull.Value)
                //                {
                //                    if (drResrult1["PolicyNumber"].ToString().Trim() != "") policynumber = drResrult1["PolicyNumber"].ToString().Trim();
                //                }

                //                if (drResrult1["CarPlate"] != DBNull.Value)
                //                {
                //                    if (drResrult1["CarPlate"].ToString().Trim() != "") carplateno = drResrult1["CarPlate"].ToString().Trim();
                //                }

                //                _emailBody = new StringBuilder();
                //                if (drResrult1["LangOption"].ToString() == "2")
                //                {
                //                    //English
                //                    if (carplateno.Length > 2)
                //                    {
                //                        //_emailTitle = "Renewal Reminder - Your motor policy for license plate " + carplateno + " is expiring soon. Renew today!";
                //                        _emailTitle = "Renewal Reminder [" + carplateno + "] - GluayGluay.com";
                //                    }
                //                    else
                //                    {
                //                        //_emailTitle = "Renewal Reminder: Ref No." + drResrult1["QuoteRefNo"].ToString() + " - Your motor policy is expiring soon. Renew today!";
                //                        _emailTitle = "Renewal Reminder [" + drResrult1["QuoteRefNo"].ToString() + "] - GluayGluay.com";
                //                    }

                //                    if (drResrult1["Title"] != DBNull.Value && drResrult1["Title"].ToString().Trim().Length > 1)
                //                    {
                //                        //[YUI] @27/08/2013 remove title " + drResrult1["Title"].ToString() + "
                //                        _emailBody.Append("Dear <b>Khun " + drResrult1["CustomerName"].ToString() + "</b>,");
                //                    }
                //                    else
                //                    {
                //                        _emailBody.Append("Dear <b>" + drResrult1["CustomerName"].ToString() + "</b>,");
                //                    }

                //                    DateTime _CoverageStart = Convert.ToDateTime(drResrult1["CoverageStart"]);
                //                    DateTime _CTPLStart = Convert.ToDateTime(drResrult1["CTPLStart"]);

                //                    _emailBody.Append("<br/><br/>We would like to inform you that your car insurance will be expiring soon. ");
                //                    //_emailBody.Append("<br/><br/>Your car insurance is going to expire on " + _CoverageStart.AddYears(1).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + ". Do you want to re-new it with GluayGluay.com? ");
                //                    _emailBody.Append("<br/><br/><table border='0' style=\"padding-right:5\">");
                //                    _emailBody.Append("<tr>");
                //                    if (policynumber.Length > 0)
                //                    {
                //                        _emailBody.Append("<td><b>Policy No. / GG Ref no.</b></td>");
                //                        _emailBody.Append("<td><b>: " + policynumber + " / " + drResrult1["QuoteRefNo"].ToString() + "</b></td>");
                //                    }
                //                    else
                //                    {
                //                        _emailBody.Append("<td><b>GG Ref no.</b></td>");
                //                        _emailBody.Append("<td><b>: " + drResrult1["QuoteRefNo"].ToString() + "</b></td>");
                //                    }
                //                    //_emailBody.Append("<td>Car Make / Model</td>");
                //                    //_emailBody.Append("<td>: " + drResrult1["CarModel"].ToString() + "</td>");
                //                    _emailBody.Append("</tr>");
                //                    _emailBody.Append("<tr>");

                //                    if (carplateno.Length > 2)
                //                    {
                //                        _emailBody.Append("<td><b>License Plate</b></td>");
                //                        _emailBody.Append("<td><b>: " + drResrult1["CarPlate"].ToString() + "</b></td>");
                //                    }
                //                    //_emailBody.Append("<td>Year</td>");
                //                    //_emailBody.Append("<td>: " + drResrult1["CarYear"].ToString() + "</td>");
                //                    _emailBody.Append("</tr>");
                //                    _emailBody.Append("<tr>");
                //                    _emailBody.Append("<td><b>Expiry Date</td>");
                //                    _emailBody.Append("<td><b>: " + _CoverageStart.AddYears(1).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + "</b></td>");
                //                    //_emailBody.Append("<td>Named Driver</td>");
                //                    //_emailBody.Append("<td>: " + (int.Parse(drResrult1["NoOfDriver"].ToString()) > 0 ? "Yes" : "No") + "</td>");
                //                    //_emailBody.Append("<td>Include CTPL</td>");
                //                    //_emailBody.Append("<td>: " + (drResrult1["IsCTPL"].ToString().ToLower() == "true" ? "Yes" : "No") + "</td>");
                //                    _emailBody.Append("</tr>");
                //                    _emailBody.Append("</table>");
                //                    _emailBody.Append("<br/><br/>We hope you have enjoyed our service and look forward to being of assistance for this upcoming renewal.");
                //                    _emailBody.Append("<br/><br/><span style=\"font-size:x-large\">Get <b>an early bird</b> discount off your car insurance<br/>when you renew early with us.</span>");
                //                    //_emailBody.Append("<br/>Policy Details");
                //                    //_emailBody.Append("<br/><table border=\"1\" cellpadding=\"5\" cellspacing=\"0\" style=\"border-color:Black;border-style:solid;border-width:1px\">");
                //                    //_emailBody.Append("<tr>");
                //                    //_emailBody.Append("<td style=\"width:150px;background-color:#ccc;border-color:Black;\">Policy Number</td>");
                //                    //_emailBody.Append("<td style=\"border-color:Black;\">" + drResrult1["PolicyNumber"].ToString() + "</td>");
                //                    //_emailBody.Append("</tr>");
                //                    //_emailBody.Append("<tr>");
                //                    //_emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">Insurance Company</td>");
                //                    //_emailBody.Append("<td style=\"border-color:Black;\">" + drResrult1["CompanyNameDescription"].ToString() + "</td>");
                //                    //_emailBody.Append("</tr>");
                //                    //_emailBody.Append("<tr>");
                //                    //_emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">Insurance Type</td>");
                //                    //_emailBody.Append("<td style=\"border-color:Black;\">" + GetClassNameEN(drResrult1["InsuranceType"].ToString()) + "</td>");
                //                    //_emailBody.Append("</tr>");
                //                    //_emailBody.Append("<tr>");
                //                    //_emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">Package Name</td>");
                //                    //_emailBody.Append("<td style=\"border-color:Black;\">" + drResrult1["Package"].ToString() + "</td>");
                //                    //_emailBody.Append("</tr>");
                //                    //_emailBody.Append("<tr>");
                //                    //_emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">Capital Insured</td>");
                //                    //_emailBody.Append("<td style=\"border-color:Black;\">" + Convert.ToDecimal(drResrult1["SumInsured"]).ToString("#,##0.00") + "</td>");
                //                    //_emailBody.Append("</tr>");
                //                    //_emailBody.Append("<tr>");
                //                    //_emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">Premium</td>");
                //                    //_emailBody.Append("<td style=\"border-color:Black;\">" + Convert.ToDecimal(drResrult1["Premium"]).ToString("#,##0.00") + "</td>");
                //                    //_emailBody.Append("</tr>");
                //                    //_emailBody.Append("<tr>");
                //                    //_emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">Policy Start Date</td>");
                //                    //_emailBody.Append("<td style=\"border-color:Black;\">" + _CoverageStart.ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + "</td>");
                //                    //_emailBody.Append("</tr>");
                //                    //if (drResrult1["IsCTPL"].ToString().ToLower() == "true")
                //                    //{
                //                    //    _emailBody.Append("<tr>");
                //                    //    _emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">CTPL Amount</td>");
                //                    //    _emailBody.Append("<td style=\"border-color:Black;\">" + Convert.ToDecimal(drResrult1["CTPL"]).ToString("#,##0.00") + "</td>");
                //                    //    _emailBody.Append("</tr>");
                //                    //    _emailBody.Append("<tr>");
                //                    //    _emailBody.Append("<td style=\"background-color:#ccc;border-color:Black;\">CTPL Start Date</td>");
                //                    //    _emailBody.Append("<td style=\"border-color:Black;\">" + _CTPLStart.ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + "</td>");
                //                    //    _emailBody.Append("</tr>");
                //                    //}
                //                    //_emailBody.Append("</table>");

                //                    _emailBody.Append("<br/><br/>We are ready to assist at hotline: <strong>02-268-8222</strong> or renewal direct line at <strong>02-646-9110</strong> Monday to Fridays from 9am to 6pm. Alternatively, you can email us at <strong>support@gluaygluay.com.</strong>");
                //                    _emailBody.Append("<br/><br/>we are apologize if you already renew your policy.");
                //                    _emailBody.Append("<br/><br/>Best regards,");
                //                    _emailBody.Append("<br/><br/>Support GluayGluay.com");
                //                    _emailBody.Append("<br/><br/>CompareXpress Insurance Broker (Thailand) Ltd. ");
                //                    _emailBody.Append("<br/>OIC Licensed Non-Life Insurance Broker: ว00016/2553");
                //                    _emailBody.Append("<br/>2 K.C.C. Building 2nd floor Silom Soi 9 Silom Road Bangrak Bangkok 10500");
                //                    _emailBody.Append("<br/>W: <a href='http://www.GluayGluay.com'>www.GluayGluay.com</a> | T: 02-268 8222 | E: support@gluaygluay.com ");
                //                    _emailBody.Append("<br/>Follow us: <a href='http://www.facebook.com/GGmotor'>www.facebook.com/GGmotor</a> | Instagram: #GGmotor ");
                //                    _emailBody.Append("<br/><br/>");

                //                    _emailBody.Append("<a href='http://www.GluayGluay.com'><img src='http://www.gluaygluay.com/images/public/logo/gluaygluay-plakat_export.jpg' alt='GluayGluay.com'/></a>");
                //                    //_emailBody.Append("<br/>GluayGluay.com.<br/>");
                //                    //_emailBody.Append("http://www.GluayGluay.com<br/><br/>");

                //                    bool _emailSend = false;
                //                    while (!_emailSend)
                //                    {
                //                        if (MyMailManager.SendGGMail(_smtpAccount, _smtpAuthCode, _toemail, _ccemail, _bccemail, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                //                        {
                //                            _mailThreeSendCount++;
                //                            _emailSend = true;
                //                        }
                //                        else
                //                        {
                //                            _eventLog.WriteEntry(_serviceTitle + " Failed sending to " + _toemail + " at [" + dateTimeNow.ToString() + "].", EventLogEntryType.Error);
                //                            System.Threading.Thread.Sleep(_emailIntervalSec * 3000);
                //                        }
                //                    }

                //                    System.Threading.Thread.Sleep(_emailIntervalSec * 1000);
                //                }
                //                else if (drResrult1["LangOption"].ToString() == "1")
                //                {
                //                    //Thai
                //                    if (carplateno.Length > 2)
                //                    {
                //                        //_emailTitle = "แจ้งเตือนการต่ออายุ – ประกันภัยรถยนตร์ของคุณ ทะเบียน " + carplateno + " จะหมดอายุเร็วๆนี้ ต่ออายุได้เลยวันนี้";
                //                        _emailTitle = "แจ้งเตือนการต่ออายุประกัน ทะเบียน " + carplateno + " - GluayGluay.com";
                //                    }
                //                    else
                //                    {
                //                        //_emailTitle = "แจ้งเตือนการต่ออายุ - เลขอ้างอิง " + drResrult1["QuoteRefNo"].ToString() + " – ประกันภัยรถยนตร์ของคุณจะหมดอายุเร็วๆนี้ ต่ออายุได้เลยวันนี้";
                //                        _emailTitle = "แจ้งเตือนการต่ออายุประกัน เลขที่อ้างอิง " + drResrult1["QuoteRefNo"].ToString() + " - GluayGluay.com";
                //                    }

                //                    if (drResrult1["Title"] != DBNull.Value && drResrult1["Title"].ToString().Trim().Length > 1)
                //                    {
                //                        //[YUI] @19/08/2013 remove title " + drResrult1["Title"].ToString() + "
                //                        _emailBody.Append("เรียน <b>คุณ " + drResrult1["CustomerName"].ToString() + "</b>,");
                //                    }
                //                    else
                //                    {
                //                        _emailBody.Append("เรียน <b>" + drResrult1["CustomerName"].ToString() + "</b>,");
                //                    }

                //                    DateTime _CoverageStart = Convert.ToDateTime(drResrult1["CoverageStart"]);
                //                    DateTime _CTPLStart = Convert.ToDateTime(drResrult1["CTPLStart"]);

                //                    _emailBody.Append("<br/><br/>เราต้องการเรียนแจ้งให้คุณทราบว่ากรมธรรม์ประกันภัยรถยนต์ของคุณใกล้จะหมดอายุเร็ว ๆ นี้ ");
                //                    _emailBody.Append("<br/><br/><table border='0' style=\"padding-right:5\">");
                //                    _emailBody.Append("<tr>");
                //                    if (policynumber.Length > 0)
                //                    {
                //                        _emailBody.Append("<td><b>หมายเลขกรมธรรม์ / เลขอ้างอิง GluayGluay.com</b></td>");
                //                        _emailBody.Append("<td><b>: " + policynumber + " / " + drResrult1["QuoteRefNo"].ToString() + "</b></td>");
                //                    }
                //                    else
                //                    {
                //                        _emailBody.Append("<td><b>เลขอ้างอิง GluayGluay.com</b></td>");
                //                        _emailBody.Append("<td><b>: " + drResrult1["QuoteRefNo"].ToString() + "</b></td>");
                //                    }
                //                    _emailBody.Append("</tr>");
                //                    _emailBody.Append("<tr>");

                //                    if (carplateno.Length > 2)
                //                    {
                //                        _emailBody.Append("<td><b>ทะเบียนรถ</b></td>");
                //                        _emailBody.Append("<td><b>: " + drResrult1["CarPlate"].ToString() + "</b></td>");
                //                    }
                //                    _emailBody.Append("</tr>");
                //                    _emailBody.Append("<tr>");
                //                    _emailBody.Append("<td><b>วันที่หมดอายุ</td>");
                //                    _emailBody.Append("<td><b>: " + _CoverageStart.AddYears(1).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + "</b></td>");
                //                    _emailBody.Append("</tr>");
                //                    _emailBody.Append("</table>");
                //                    _emailBody.Append("<br/><br/>เราหวังว่าคุณจะพึงพอใจในการให้บริการของเราและหวังเป็นอย่างยิ่งว่าจะได้ช่วยเหลือคุณในการต่ออายุประกันภัยรถยนตร์สำหรับปีนี้และต่อๆไป");
                //                    _emailBody.Append("<br/><br/><span style=\"font-size:x-large\">รับ <b>ส่วนลดพิเศษเพิ่ม</b> เมื่อคุณต่ออายุประกันภัยรถยนต์กับเราล่วงหน้า</span>");

                //                    _emailBody.Append("<br/><br/>เราพร้อมที่จะช่วยเหลือและให้บริการคุณ โปรดติดต่อที่เบอร์กลาง <strong>02-268-8222</strong> หรือ ติดต่อเจ้าหน้าที่งานต่ออายุโดยตรงที่เบอร์ <strong>02-646-9110</strong> วันจันทร์ ถึง วันศุกร์ เวลา 9.00 – 18.00 หรือส่งอีเมล์มาหาเราที่  <strong>support@gluaygluay.com.</strong>");
                //                    _emailBody.Append("<br/><br/>ขออภัยหากคุณได้ทำการต่ออายุประกันแล้ว");
                //                    _emailBody.Append("<br/><br/>ด้วยความนับถือ,");
                //                    _emailBody.Append("<br/><br/>ทีมงาน GluayGluay.com");
                //                    _emailBody.Append("<br/><br/>บริษัท คอมแพร์เอ็กซ์เพรส นายหน้าประกันภัย (ประเทศไทย) จำกัด");
                //                    _emailBody.Append("<br/>เลขทะเบียนนายหน้าประกันวินาศภัยนิติบุคคล เลขที่ ว00016/2553");
                //                    _emailBody.Append("<br/>2 อาคาร เค.ซี.ซี. ชั้น 2 สีสมซอย 9 ถนนสีลม เขตบางรัก 10500");
                //                    _emailBody.Append("<br/>เว็บไซต์: <a href='http://www.GluayGluay.com'>www.GluayGluay.com</a> | โทร.: 02-268 8222 | อีเมล์: support@gluaygluay.com ");
                //                    _emailBody.Append("<br/>Follow us: <a href='http://www.facebook.com/GGmotor'>www.facebook.com/GGmotor</a> | Instagram: #GGmotor ");
                //                    _emailBody.Append("<br/><br/>");

                //                    _emailBody.Append("<a href='http://www.GluayGluay.com'><img src='http://www.gluaygluay.com/images/public/logo/gluaygluay-plakat_export.jpg' alt='GluayGluay.com'/></a>");

                //                    bool _emailSend = false;

                //                    while (!_emailSend)
                //                    {
                //                        if (MyMailManager.SendGGMail(_smtpAccount, _smtpAuthCode, _toemail, _ccemail, _bccemail, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                //                        {
                //                            _mailThreeSendCount++;
                //                            _emailSend = true;
                //                        }
                //                        else
                //                        {
                //                            _eventLog.WriteEntry(_serviceTitle + " Failed sending to " + _toemail + " at [" + dateTimeNow.ToString() + "].", EventLogEntryType.Error);
                //                            System.Threading.Thread.Sleep(_emailIntervalSec * 3000);
                //                        }
                //                    }

                //                    System.Threading.Thread.Sleep(_emailIntervalSec * 1000);
                //                }

                //            }
                //        }

                //        _step = "Step 2.2";
                //        foreach (DataRow drResrult2 in _dsResult.Tables[1].Rows)
                //        {
                //            //Before 1 months
                //            if (IsLive == "1")
                //            {
                //                _toemail = drResrult2["Email"].ToString();
                //            }

                //            if (_toemail.Trim() != "")
                //            {
                //                string carplateno = "";
                //                string policynumber = "";
                //                if (drResrult2["PolicyNumber"] != DBNull.Value)
                //                {
                //                    if (drResrult2["PolicyNumber"].ToString().Trim() != "") policynumber = drResrult2["PolicyNumber"].ToString().Trim();
                //                }
                //                if (drResrult2["CarPlate"] != DBNull.Value)
                //                {
                //                    if (drResrult2["CarPlate"].ToString().Trim() != "") carplateno = drResrult2["CarPlate"].ToString().Trim();
                //                }

                //                _emailBody = new StringBuilder();
                //                if (drResrult2["LangOption"].ToString() == "2")
                //                {
                //                    //English
                //                    if (carplateno.Length > 2)
                //                    {
                //                        //_emailTitle = "Renewal Reminder - Your motor policy for license plate " + carplateno + " is expiring soon. Renew today!";
                //                        _emailTitle = "Renewal Reminder [" + carplateno + "] - GluayGluay.com";
                //                    }
                //                    else
                //                    {
                //                        //_emailTitle = "Renewal Reminder: Ref No." + drResrult1["QuoteRefNo"].ToString() + " - Your motor policy is expiring soon. Renew today!";
                //                        _emailTitle = "Renewal Reminder [" + drResrult2["QuoteRefNo"].ToString() + "] - GluayGluay.com";
                //                    }

                //                    if (drResrult2["Title"] != DBNull.Value && drResrult2["Title"].ToString().Trim().Length > 1)
                //                    {
                //                        //[YUI] @27/08/2013 remove title " + drResrult1["Title"].ToString() + "
                //                        _emailBody.Append("Dear <b>Khun " + drResrult2["CustomerName"].ToString() + "</b>,");
                //                    }
                //                    else
                //                    {
                //                        _emailBody.Append("Dear <b>" + drResrult2["CustomerName"].ToString() + "</b>,");
                //                    }

                //                    DateTime _CoverageStart = Convert.ToDateTime(drResrult2["CoverageStart"]);
                //                    DateTime _CTPLStart = Convert.ToDateTime(drResrult2["CTPLStart"]);

                //                    _emailBody.Append("<br/><br/>We would like to inform you that your car insurance will be expiring soon. ");
                //                    _emailBody.Append("<br/><br/><table border='0' style=\"padding-right:5\">");
                //                    _emailBody.Append("<tr>");
                //                    if (policynumber.Length > 0)
                //                    {
                //                        _emailBody.Append("<td><b>Policy No. / GG Ref no.</b></td>");
                //                        _emailBody.Append("<td><b>: " + policynumber + " / " + drResrult2["QuoteRefNo"].ToString() + "</b></td>");
                //                    }
                //                    else
                //                    {
                //                        _emailBody.Append("<td><b>GG Ref no.</b></td>");
                //                        _emailBody.Append("<td><b>: " + drResrult2["QuoteRefNo"].ToString() + "</b></td>");
                //                    }
                //                    _emailBody.Append("</tr>");
                //                    _emailBody.Append("<tr>");
                //                    _emailBody.Append("<td><b>License Plate</b></td>");
                //                    _emailBody.Append("<td><b>: " + drResrult2["CarPlate"].ToString() + "</b></td>");
                //                    _emailBody.Append("</tr>");
                //                    _emailBody.Append("<tr>");
                //                    _emailBody.Append("<td><b>Expiry Date</td>");
                //                    _emailBody.Append("<td><b>: " + _CoverageStart.AddYears(1).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + "</b></td>");
                //                    _emailBody.Append("</tr>");
                //                    _emailBody.Append("</table>");
                //                    _emailBody.Append("<br/><br/>We hope you have enjoyed our service and look forward to being of assistance for this upcoming renewal.");
                //                    //send second time no need this line
                //                    //_emailBody.Append("<br/><br/><span style=\"font-size:x-large\">Get an <b>early bird</b> discount off your car insurance when you renew early with us.</span>");

                //                    _emailBody.Append("<br/><br/>We are ready to assist at hotline: <strong>02-268-8222</strong> or renewal direct line at <strong>02-646-9110</strong> Monday to Fridays from 9am to 6pm. Alternatively, you can email us at <strong>support@gluaygluay.com.</strong>");
                //                    _emailBody.Append("<br/><br/>we are apologize if you already renew your policy.");
                //                    _emailBody.Append("<br/><br/>Best regards,");
                //                    _emailBody.Append("<br/><br/>Support GluayGluay.com");
                //                    _emailBody.Append("<br/><br/>CompareXpress Insurance Broker (Thailand) Ltd. ");
                //                    _emailBody.Append("<br/>OIC Licensed Non-Life Insurance Broker: ว00016/2553");
                //                    _emailBody.Append("<br/>2 K.C.C. Building 2nd floor Silom Soi 9 Silom Road Bangrak Bangkok 10500");
                //                    _emailBody.Append("<br/>W: <a href='http://www.GluayGluay.com'>www.GluayGluay.com</a> | T: 02-268 8222 | E: support@gluaygluay.com ");
                //                    _emailBody.Append("<br/>Follow us: <a href='http://www.facebook.com/GGmotor'>www.facebook.com/GGmotor</a> | Instagram: #GGmotor ");
                //                    _emailBody.Append("<br/><br/>");

                //                    _emailBody.Append("<a href='http://www.GluayGluay.com'><img src='http://www.gluaygluay.com/images/public/logo/gluaygluay-plakat_export.jpg' alt='GluayGluay.com'/></a>");

                //                    bool _emailSend = false;
                //                    while (!_emailSend)
                //                    {
                //                        if (MyMailManager.SendGGMail(_smtpAccount, _smtpAuthCode, _toemail, _ccemail, _bccemail, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                //                        {
                //                            _mailOneSendCount++;
                //                            _emailSend = true;
                //                        }
                //                        else
                //                        {
                //                            _eventLog.WriteEntry(_serviceTitle + " Failed sending to " + _toemail + " at [" + dateTimeNow.ToString() + "].", EventLogEntryType.Error);
                //                            System.Threading.Thread.Sleep(_emailIntervalSec * 3000);
                //                        }
                //                    }

                //                    System.Threading.Thread.Sleep(_emailIntervalSec * 1000);
                //                }
                //                else if (drResrult2["LangOption"].ToString() == "1")
                //                {
                //                    //Thai
                //                    if (policynumber.Length != 0 && carplateno.Length > 2)
                //                    {
                //                        //_emailTitle = "แจ้งเตือนการต่ออายุ – ประกันภัยรถยนตร์ของคุณ ทะเบียน " + carplateno + " จะหมดอายุเร็วๆนี้ ต่ออายุได้เลยวันนี้";
                //                        _emailTitle = "แจ้งเตือนการต่ออายุประกัน ทะเบียน " + carplateno + " - GluayGluay.com";
                //                    }
                //                    else
                //                    {
                //                        //_emailTitle = "แจ้งเตือนการต่ออายุ - เลขอ้างอิง " + drResrult1["QuoteRefNo"].ToString() + " – ประกันภัยรถยนตร์ของคุณจะหมดอายุเร็วๆนี้ ต่ออายุได้เลยวันนี้";
                //                        _emailTitle = "แจ้งเตือนการต่ออายุประกัน เลขที่อ้างอิง " + drResrult2["QuoteRefNo"].ToString() + " - GluayGluay.com";
                //                    }

                //                    if (drResrult2["Title"] != DBNull.Value && drResrult2["Title"].ToString().Trim().Length > 1)
                //                    {
                //                        ////[YUI] @19/08/2013 remove title " + drResrult2["Title"].ToString() + "
                //                        _emailBody.Append("เรียน <b>คุณ " + drResrult2["CustomerName"].ToString() + "</b>,");
                //                    }
                //                    else
                //                    {
                //                        _emailBody.Append("เรียน <b>" + drResrult2["CustomerName"].ToString() + "</b>,");
                //                    }

                //                    DateTime _CoverageStart = Convert.ToDateTime(drResrult2["CoverageStart"]);
                //                    DateTime _CTPLStart = Convert.ToDateTime(drResrult2["CTPLStart"]);

                //                    _emailBody.Append("<br/><br/>เราต้องการเรียนแจ้งให้คุณทราบว่ากรมธรรม์ประกันภัยรถยนต์ของคุณใกล้จะหมดอายุเร็ว ๆ นี้ ");
                //                    _emailBody.Append("<br/><br/><table border='0' style=\"padding-right:5\">");
                //                    _emailBody.Append("<tr>");
                //                    if (policynumber.Length > 0)
                //                    {
                //                        _emailBody.Append("<td><b>หมายเลขกรมธรรม์ / เลขอ้างอิง GluayGluay.com</b></td>");
                //                        _emailBody.Append("<td><b>: " + policynumber + " / " + drResrult2["QuoteRefNo"].ToString() + "</b></td>");
                //                    }
                //                    else
                //                    {
                //                        _emailBody.Append("<td><b>เลขอ้างอิง GluayGluay.com</b></td>");
                //                        _emailBody.Append("<td><b>: " + drResrult2["QuoteRefNo"].ToString() + "</b></td>");
                //                    }
                //                    _emailBody.Append("</tr>");
                //                    _emailBody.Append("<tr>");
                //                    _emailBody.Append("<td><b>ทะเบียนรถ</b></td>");
                //                    _emailBody.Append("<td><b>: " + drResrult2["CarPlate"].ToString() + "</b></td>");
                //                    _emailBody.Append("</tr>");
                //                    _emailBody.Append("<tr>");
                //                    _emailBody.Append("<td><b>วันที่หมดอายุ</td>");
                //                    _emailBody.Append("<td><b>: " + _CoverageStart.AddYears(1).ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")) + "</b></td>");
                //                    _emailBody.Append("</tr>");
                //                    _emailBody.Append("</table>");
                //                    _emailBody.Append("<br/><br/>เราหวังว่าคุณจะพึงพอใจในการให้บริการของเราและหวังเป็นอย่างยิ่งว่าจะได้ช่วยเหลือคุณในการต่ออายุประกันภัยรถยนตร์สำหรับปีนี้และต่อๆไป");
                //                    //send second time no need this line
                //                    //_emailBody.Append("<br/><br/><span style=\"font-size:x-large\">รับ <b>ส่วนลดพิเศษเพิ่ม</b> เมื่อคุณต่ออายุประกันภัยรถยนต์กับเราล่วงหน้า</span>");

                //                    _emailBody.Append("<br/><br/>เราพร้อมที่จะช่วยเหลือและให้บริการคุณ โปรดติดต่อที่เบอร์กลาง <strong>02-268-8222</strong> หรือ ติดต่อเจ้าหน้าที่งานต่ออายุโดยตรงที่เบอร์ <strong>02-646-9110</strong> วันจันทร์ ถึง วันศุกร์ เวลา 9.00 – 18.00 หรือส่งอีเมล์มาหาเราที่  <strong>support@gluaygluay.com.</strong>");
                //                    _emailBody.Append("<br/><br/>ขออภัยหากคุณได้ทำการต่ออายุประกันแล้ว");
                //                    _emailBody.Append("<br/><br/>ด้วยความนับถือ,");
                //                    _emailBody.Append("<br/><br/>ทีมงาน GluayGluay.com");
                //                    _emailBody.Append("<br/><br/>บริษัท คอมแพร์เอ็กซ์เพรส นายหน้าประกันภัย (ประเทศไทย) จำกัด");
                //                    _emailBody.Append("<br/>เลขทะเบียนนายหน้าประกันวินาศภัยนิติบุคคล เลขที่ ว00016/2553");
                //                    _emailBody.Append("<br/>2 อาคาร เค.ซี.ซี. ชั้น 2 สีสมซอย 9 ถนนสีลม เขตบางรัก 10500");
                //                    _emailBody.Append("<br/>เว็บไซต์: <a href='http://www.GluayGluay.com'>www.GluayGluay.com</a> | โทร.: 02-268 8222 | อีเมล์: support@gluaygluay.com ");
                //                    _emailBody.Append("<br/>Follow us: <a href='http://www.facebook.com/GGmotor'>www.facebook.com/GGmotor</a> | Instagram: #GGmotor ");
                //                    _emailBody.Append("<br/><br/>");

                //                    _emailBody.Append("<a href='http://www.GluayGluay.com'><img src='http://www.gluaygluay.com/images/public/logo/gluaygluay-plakat_export.jpg' alt='GluayGluay.com'/></a>");

                //                    bool _emailSend = false;

                //                    while (!_emailSend)
                //                    {
                //                        if (MyMailManager.SendGGMail(_smtpAccount, _smtpAuthCode, _toemail, _ccemail, _bccemail, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                //                        {
                //                            _mailOneSendCount++;
                //                            _emailSend = true;
                //                        }
                //                        else
                //                        {
                //                            _eventLog.WriteEntry(_serviceTitle + " Failed sending to " + _toemail + " at [" + dateTimeNow.ToString() + "].", EventLogEntryType.Error);
                //                            System.Threading.Thread.Sleep(_emailIntervalSec * 3000);
                //                        }
                //                    }

                //                    System.Threading.Thread.Sleep(_emailIntervalSec * 1000);
                //                }
                //            }
                //        }
                //    }

                //    if (_dsResult.Tables[0].Rows.Count > 0 || _dsResult.Tables[1].Rows.Count > 0)
                //    {
                //        _eventLog.WriteEntry(_serviceTitle + " Finished at [" + dateTimeNow.ToString("dd/MMM/yyyy hh:mm:ss tt") + "].", EventLogEntryType.Information);

                //        _toemail = System.Configuration.ConfigurationSettings.AppSettings["ToEmail"].ToString();
                //        _ccemail = System.Configuration.ConfigurationSettings.AppSettings["CcEmail"].ToString();
                //        _bccemail = System.Configuration.ConfigurationSettings.AppSettings["BccEmail"].ToString();
                //        _emailTitle = "<Finished> " + _serviceTitle + " - " + dateTimeNow.ToString("dd-MMM-yyyy hh:mm tt");
                //        _emailBody = new StringBuilder();
                //        _emailBody.Append("Dear All,<br/><br/>");
                //        _emailBody.Append(_serviceTitle + " was finished at " + dateTimeNow.ToString("dd/MMM/yyyy hh:mm:ss tt") + "<br/><br/>");

                //        if (IsSendEmailCustomer == "1")
                //        {
                //            _emailBody.Append("No. of 3 months successfully sent mail : <b>" + _mailThreeSendCount.ToString() + "</b><br/><br/>");
                //            _emailBody.Append("No. of 1 month successfully sent mail : <b>" + _mailOneSendCount.ToString() + "</b><br/><br/>");
                //        }
                //        else
                //        {
                //            _emailBody.Append("Is send Email Customer : <b>" + IsSendEmailCustomer + "</b><br/><br/>");
                //        }

                //        System.Threading.Thread.Sleep(_emailIntervalSec * 1000);

                //        while (!MyMailManager.SendGGMail(_smtpInternalAccount, _smtpInternalAuthCode, _toemail, _ccemail, _bccemail, _emailTitle, _emailBody.ToString(), _smtpReplyTo))
                //        {
                //            _eventLog.WriteEntry(_serviceTitle + " - Failed to send FINISH MAIL to GG-Team at [" + dateTimeNow.ToString() + "].", EventLogEntryType.Error);
                //            System.Threading.Thread.Sleep(_emailIntervalSec * 3000);
                //        }
                //    }
                //}

                #endregion
            }
            catch (Exception Ex)
            {
                _eventLog.WriteEntry(_step + ", Message:" + Ex.Message, EventLogEntryType.Error);
            }
            finally
            {
                if (_sqlCmd != null)
                {
                    _sqlCmd = null;
                }

                if (_sqlConn.State != ConnectionState.Closed)
                {
                    _sqlConn.Close();
                    _sqlConn = null;
                }

                this.Stop();
            }
        }

        private string GetClassNameEN(string ClassId)
        {
            switch (ClassId.Trim())
            {
                case "10":
                    return "Class 1";
                case "11":
                    return "Class 1 (Low Cost)";
                case "21":
                    return "Class 2+";
                case "31":
                    return "Class 3+";
                case "30":
                    return "Class 3";
            }

            return "";
        }

        private string GetClassNameTH(string ClassId)
        {
            switch (ClassId.Trim())
            {
                case "10":
                    return "ประกันชั้น 1";
                case "11":
                    return "ประกันชั้น 1 (ประหยัด)";
                case "21":
                    return "ประกันชั้น 2+";
                case "31":
                    return "ประกันชั้น 3+";
                case "30":
                    return "ประกันชั้น 3";
            }

            return "";
        }

    }
}
